package model;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

import javax.naming.NamingException;
import javax.sql.RowSet;
import javax.sql.rowset.CachedRowSet;

import com.sun.rowset.CachedRowSetImpl;

import db.DbConnectionPool;
import db.DbException;
import forms.ApplicantForm;
import forms.Degree;
import forms.ReviewerForm;
import model.ApplicationModel;
import model.ReviewerModel;


public class SubmissionModel {
	private static String selectGrades = "SELECT count(application) FROM applications WHERE application = ? GROUP BY application";
	private static String insertGrade= "INSERT INTO applications (application, grade, comment, reviewer, ARP) VALUES (?, ?, ?, ?, 'Pending')";
	private static String getARP = "SELECT DISTINCT ARP FROM applications WHERE application = ?";
	
	public static void submitGrade(int appid, int grade, String comment, String reviewer) throws DbException
	{
		try {
			Connection conn = DbConnectionPool.getConnection();
			  
			PreparedStatement pStmt = conn.prepareStatement(insertGrade);
			
			PreparedStatement pStmt2 = conn.prepareStatement("SELECT user_ref FROM user_roles WHERE user_name='"+reviewer+"'");
			ResultSet reviewerID = pStmt2.executeQuery();
			reviewerID.next();
			int rID = reviewerID.getInt("user_ref");
			
			pStmt.setInt(1, appid);
			pStmt.setInt(2, grade);
			pStmt.setString(3, comment);
			pStmt.setInt(4, rID);
			pStmt.executeUpdate();
			
			pStmt2 = conn.prepareStatement("UPDATE reviewers_apps SET graded='true' WHERE application = '"+appid+"'");
			pStmt2.executeUpdate();
			
			pStmt.close();
			pStmt2.close();
			conn.close();

		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
	
	public static boolean getReviewerCount(int appid)throws DbException
	{
		try {
			Connection conn = DbConnectionPool.getConnection();
			
			PreparedStatement pStmt = conn.prepareStatement("SELECT count(application) FROM applications WHERE application='"+appid+"' GROUP BY application");
			ResultSet count = pStmt.executeQuery();
			
			count.next();
			
			int numOfReviewers = count.getInt("count");
			
			pStmt.close();
			conn.close();
			
			if (numOfReviewers <3)
				return true;
			else 
				return false;

		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
	
	
	public static void addReviewer(String reviewer, int appid) throws DbException
	{
		try {
			Connection conn = DbConnectionPool.getConnection();
		
			Statement statement = conn.createStatement();
			ResultSet reviewerID =statement.executeQuery("SELECT user_ref FROM user_roles WHERE user_name = '"+reviewer+"'");
			reviewerID.next();
			int rID = reviewerID.getInt("user_ref");
			
			PreparedStatement pStmt = conn.prepareStatement("INSERT INTO reviewers_apps (reviewer, application, graded) VALUES ("+rID+","+appid+", 'false')");
			pStmt.executeUpdate();
			
			conn.commit();
			pStmt.close();
			conn.close();

		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
	
	public static void decideAppStatus(String ARP, int appid) throws DbException
	{
		try {
			Connection conn = DbConnectionPool.getConnection();
			
			PreparedStatement pStmt = conn.prepareStatement("UPDATE applications SET ARP ='"+ARP+"' WHERE application = '"+appid+"'");
			pStmt.executeUpdate();
			
			conn.commit();
			pStmt.close();
			conn.close();

		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
	
	public static String getApplicationStatusFor(int appID) throws DbException{
		try{
			Connection conn = DbConnectionPool.getConnection();

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(getARP);
			pStmt.setInt(1, appID);
	
			// Use the created statement to SELECT
			// the student attributes FROM the Student table.
			ResultSet ARP = pStmt.executeQuery();
	
			CachedRowSet crsARP = new CachedRowSetImpl();
			crsARP.populate(ARP);
			crsARP.next();
			String theARP = crsARP.getString("arp");
	
			ARP.close();
			pStmt.close();
			conn.close();
	
			return theARP;
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
}
